alter table jms_dm.dm_mng_explosion_warehouse_network_mid_dt
    set tblproperties ('external.table.purge' = 'true');
drop table jms_dm.dm_mng_explosion_warehouse_network_mid_dt;
create external table jms_dm.dm_mng_explosion_warehouse_network_mid_dt
(
    final_sign_network_code string comment '最终签收派件网点编码',
    final_sign_network_name string comment '最终签收派件网点编码',
    end_agent_code          string comment '末端网点所属代理区编码',
    end_agent_name          string comment '末端网点所属代理区名称',
    need_sign_count_1d      int comment '1天应签收',
    retention_count_1d      int comment '1天内滞留',
    need_sign_count_2d      int comment '2天应签收',
    retention_count_2d      int comment '2天内滞留',
    need_sign_count_3d      int comment '3天应签收',
    retention_count_3d      int comment '3天内滞留',
    need_sign_count_5d      int comment '5天应签收',
    retention_count_5d      int comment '5天内滞留',
    need_sign_count_7d      int comment '7天应签收',
    retention_count_7d      int comment '7天内滞留',
    scan_sum                int comment '时效签收量',
    yali_flag               string comment '1代表压力值满足的要保留爆仓数据',
    final_plan_sign_date    string comment '规划签收日期'

) comment '网点滞留预警小汇总中间表T-1'
    partitioned by (dt string comment 't-1')
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_dm.db/external/dm_mng_explosion_warehouse_network_mid_dt'
;
alter table jms_dm.dm_mng_explosion_warehouse_network_mid_dt
    add columns (
        end_city_id string comment '末端网点所属城市id'
        , end_city_name string comment '末端网点所属城市名称'
        , district_code string comment '转单责任网点片区code'
        , district_desc string comment '转单责任网点片区desc'
        , district_id string comment '转单责任网点片区id'
        ) cascade;
alter table jms_dm.dm_mng_explosion_warehouse_network_mid_dt
    add columns (
        area_code string comment '区域code',
        area_name string comment '区域名字'
        ) cascade;
